package dr.member.dao;
 
import java.sql.*;

import javax.sql.*; 
import javax.naming.*;

import dr.member.domain.Member;
  
 public class MemberDAO {
    
 	private static MemberDAO instance = new MemberDAO();
    
    public static MemberDAO getInstance() {
        return instance;
    }
    
    private MemberDAO() {   
    }
     
    private Connection getConnection() throws Exception {
      Context initCtx = new InitialContext();
      Context envCtx = (Context) initCtx.lookup("java:comp/env");
      DataSource ds = (DataSource)envCtx.lookup("jdbc/orcl");
      return ds.getConnection();
    }
 
    public void insertMember(Member member) 
    throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = getConnection();
            
            pstmt = conn.prepareStatement(
            	"insert into MEMBER values (?,?,?,?,?,?,?,?,?,?)");
            pstmt.setString(1, member.getId());
            pstmt.setString(2, member.getPw());
            pstmt.setString(3, member.getName());
            pstmt.setString(4, member.getJumin());
            pstmt.setString(5, member.getZip());
            pstmt.setString(6, member.getAddr1());
            pstmt.setString(7, member.getAddr2());
            pstmt.setString(8, member.getPhone());
            pstmt.setString(9, member.getEmail());
			pstmt.setTimestamp(10, member.getReg_date());
            pstmt.executeUpdate();
        } catch(Exception ex) {
            ex.printStackTrace();
        } finally {
            if (pstmt != null) try { pstmt.close(); } catch(SQLException ex) {}
            if (conn != null) try { conn.close(); } catch(SQLException ex) {}
        }
    }
    public int userCheck(String id,String pw)
    throws Exception
    {
    	Connection conn=null;
    	PreparedStatement pstmt=null;
    	ResultSet rs=null;
    	String dbpw="";
    	int x=-1;
    	System.out.println(pw);
    	try
    	{
    		conn=getConnection();
    		pstmt=conn.prepareStatement("select pw from member where id=?");
    		pstmt.setString(1,id);
    		rs=pstmt.executeQuery();
    		
    		if(rs.next())
    		{
    			dbpw=rs.getString("pw");
    			if(dbpw.equals(pw))
    			{
    				x=1;
    			}
    			else
    			{
    				x=0;
    			}
    			
    		}else
    		{
    			x=-1;
    		}
    	}catch(Exception ex)
    	{
    		ex.printStackTrace();
    	}finally
    	{
    		if(rs!=null){try{rs.close();}catch(SQLException sqle){}}
    		if(pstmt!=null){try{pstmt.close();}catch(SQLException sqle){}}
    		if(conn!=null){try{conn.close();}catch(SQLException sqle){}}
    	}
    	return x;
    	
    	
    	
    }
    

    public Member getMember(String id)
    throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Member member=null;
        try {
            conn = getConnection();
            
            pstmt = conn.prepareStatement(
            	"select * from MEMBER where id = ?");
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                member = new Member();
                member.setId(rs.getString("id"));
                member.setPw(rs.getString("pw"));
				member.setName(rs.getString("name"));
                member.setJumin(rs.getString("jumin"));
                member.setZip(rs.getString("zip"));
                member.setAddr1(rs.getString("addr1"));
                member.setAddr2(rs.getString("addr2"));
                member.setPhone(rs.getString("phone"));
				member.setEmail(rs.getString("email"));
                member.setReg_date(rs.getTimestamp("reg_date"));     
			}
        } catch(Exception ex) {
            ex.printStackTrace();
        } finally {
            if (rs != null) try { rs.close(); } catch(SQLException ex) {}
            if (pstmt != null) try { pstmt.close(); } catch(SQLException ex) {}
            if (conn != null) try { conn.close(); } catch(SQLException ex) {}
        }
		return member;
    }
    
    public void updateMember(Member member)throws Exception
    {
    	Connection conn=null;
    	PreparedStatement pstmt=null;
    	try
    	{
    		conn=getConnection();
    		int cnt=0;
    		pstmt=conn.prepareStatement("update member set name=?,phone=?,zip=?,addr1=?,addr2=?,email=? where id=?");
    		
            pstmt.setString(++cnt, member.getName());
            pstmt.setString(++cnt, member.getPhone());
            pstmt.setString(++cnt, member.getZip());
            pstmt.setString(++cnt, member.getAddr1());
            pstmt.setString(++cnt, member.getAddr2());
            pstmt.setString(++cnt, member.getEmail());
            pstmt.setString(++cnt, member.getId());
    		pstmt.executeUpdate();
    		
   
    	}catch(Exception ex){
    		ex.printStackTrace();
    	}finally{
    		if(pstmt!=null){try{pstmt.close();}catch(SQLException sqle){}}
    		if(conn!=null){try{conn.close();}catch(SQLException sqle){}}
    	}
    }
    
    public int deleteMember(String id, String pw)
	throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String dbpasswd = " ";
		int x=-1;
		try{
			conn=getConnection();
			pstmt = conn.prepareStatement("select pw from MEMBER where id=?");
			pstmt.setString(1, id);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				dbpasswd=rs.getString("pw");
				if(dbpasswd.equals(pw)){
					pstmt=conn.prepareStatement("delete from MEMBER where id=?");
					pstmt.setString(1, id);
					pstmt.executeUpdate();
					x=1;
				}else
					x=0;
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if (rs != null) try { rs.close(); } catch(SQLException ex) {}
            if (pstmt != null) try { pstmt.close(); } catch(SQLException ex) {}
            if (conn != null) try { conn.close(); } catch(SQLException ex) {}
		}
	return x;
}
    
    public int confirmId(String id)throws Exception
    {
    	Connection conn=null;
    	PreparedStatement pstmt=null;
    	ResultSet rs=null;
    	int x=-1;
    	try
    	{
    		conn=getConnection();
    		pstmt=conn.prepareStatement("select id from member where id=?");
    		pstmt.setString(1,id);
    		rs=pstmt.executeQuery();
    		if(rs.next())
    		{
    			x=1;
    		}
    		else
    		{
    			x=-1;
    		}
    		
    	}catch(Exception ex)
    	{
    		ex.printStackTrace();
    	}finally
    	{
    		if(rs!=null){try{rs.close();}catch(SQLException sqle){}}
    		if(pstmt!=null){try{pstmt.close();}catch(SQLException sqle){}}
    		if(conn!=null){try{conn.close();}catch(SQLException sqle){}}
    	}
    	return x;
    	
    }
    
 }